import os
import pyodbc
import pandas as pd
import Get_HTMLtables as GH
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook
global tables
import pyodbc
import re
import os
class PDFTableExtractor:
    def __init__(self, ID, find_keyword,Html_file):
        self.ID = ID
        read_html_obj = GH.ReadHtml()  # 创建GH.ReadHtml实例
        self.ZBDW=read_html_obj.read_DW(Html_file,find_keyword)
    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                   SELECT DISTINCT EPBH
                   FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                   WHERE QYBH= ?
                       """
        IGSDM = str(self.IGSDM)
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'

            return self.EPBH
    def get_GG(self):
        server = 'SHPUBSRV01'
        database = 'JYPRIME'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        ID = self.ID
        query = """
                                  SELECT ID,IGSDM,CONVERT(varchar(10),XXFBRQ,23) AS XXFBRQ,CONVERT(varchar(10),JZRQ,23) AS JZRQ,XXBT,GGLJ
                                  FROM usrGSGGYWFWB GG 
                                  WHERE ID= CAST(? AS NVARCHAR)
                                      """
        self.GG = pd.read_sql(query, conn, params=[ID])
        if not self.GG.empty:
            self.IGSDM = self.GG['IGSDM'][0]
            self.XXFBRQ = self.GG['XXFBRQ'][0]
            self.JZRQ = self.GG['JZRQ'][0]
            self.XXLY = self.GG['XXBT'][0]
            return self.GG
    def find_keyword_and_extract_table(self, Html_file, find_keyword):
        table = GH.ReadHtml.read_html_file(Html_file, find_keyword)
        table = table.replace('主要产品', 'SJLMSMC').replace(' ','',regex=True).replace('分业务领域','分行业')
        if table[0][0]!='主营业务分行业情况'and table[0][0]=='分行业':
            VALUE=['主营业务分行业情况','','','','','','']
            table.loc[-1] = VALUE
            table.index = table.index + 1  # 更新索引
            table = table.sort_index()  # 根据索引排序
        elif table[0][0]!='主营业务分产品情况' and table[0][0]=='分产品':
            VALUE = ['主营业务分产品情况', '', '', '', '', '', '']
            table.loc[-1] = VALUE
            table.index = table.index + 1  # 更新索引
            table = table.sort_index()  # 根据索引排序
        return table
    # 切割表格(营收)
    def sp(self,table):
        keywords = ['主营业务分行业情况', '主营业务分产品情况', '主营业务分地区情况', '主营业务分销售模式情况']
        print('开始分割')
        if any(table.isin(['主营业务分行业情况', '主营业务分地区情况', '主营业务分产品情况','主营业务分销售模式情况'])):
            new_df = table
            new_df.columns=range(len(new_df.columns))
            BASCI_split_indices = [0]
            new_df[0] = new_df[0].astype(str)
            split_indices = new_df[
                new_df[0].apply(lambda x: any(keyword in x for keyword in keywords))].index.tolist()
            split_indices = [int(str(index).replace('[', '').replace(']', '')) for index in split_indices]
            split_indices = BASCI_split_indices + split_indices
            if split_indices:
                split_indices.append(len(new_df))
                with pd.ExcelWriter('分割表格.xlsx', engine='xlsxwriter') as writer:
                    for i in range(0, len(split_indices)):
                        try:
                            if split_indices[i] > 0:
                                dfs = new_df.iloc[split_indices[i - 1] + 1:split_indices[i]]
                                sheet_name = dfs[0].iloc[0]
                                dfs.to_excel(writer, sheet_name=sheet_name, index=False)
                        except:
                            pass

    # 获取代码类字段(营收)
    def get_code(self):
        db_name = r'\\10.3.2.15\固收部\其他\经营指标\自动化标准化存档\mydatabase.db'
        con = connect(db_name)
        Product = "SELECT * FROM product_code"
        self.PRODUCT_CODE = pd.read_sql(Product, con)
        ZB = 'SELECT * FROM ZB_CODE'
        self.ZB_CODE = pd.read_sql(ZB, con)
        DQ = 'SELECT * FROM city_code'
        self.DQ_CODE = pd.read_sql(DQ, con)
        BT='SELECT * FROM GG  '
        self.BT=pd.read_sql(BT,con)
        BZH='SELECT * FROM BZH  WHERE EPBH =?'
        self.BZH=pd.read_sql(BZH,con,params=[self.IGSDM])
        con.close()
    # 格式化为企业经营的底层表(营收)
    def xggs(self):
        all_merged_data=pd.DataFrame()
        excel_file_path = '分割表格.xlsx'
        new_excel_file_path = f'{self.EPBH}_{self.JZRQ}_营收表.xlsx'
        workbook = openpyxl.load_workbook(excel_file_path)
        sheet_names = workbook.sheetnames
        with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
            for i, sheet_name in enumerate(sheet_names, start=1):
                print(f'Processing sheet {i}')
                yysr = pd.read_excel(excel_file_path, sheet_name=sheet_name).replace(' ','', regex=True).replace('分产品或服务','分行业')
                # prev_index = None
                # for index,row in yysr.iterrows():
                #     for cow in row:
                #         if pd.isna(cow):
                #             n=n+1
                #             if n==3:
                #                 a=yysr.iloc[index-1]
                #                 a=a.fillna('')
                #                 b=yysr.iloc[index]
                #                 b=b.fillna('')
                #                 combined_row = a+b
                #                 yysr.iloc[index-1] = combined_row
                #                 yysr.drop(index,inplace=True)
                #                 break
                #         else:
                #             n=0
                #         prev_index=index

                if '分产品' in sheet_names and '分行业' in sheet_names and sheet_name=='分行业':
                    continue
                if sheet_name=='分行业' or sheet_name=='分产品':
                    new_columns = ['SJLMYMC','销售收入','营业成本','毛利率','销售收入同比','营业成本同比','毛利率同比']
                elif sheet_name=='分地区':
                    new_columns = ['SJLMEMC','销售收入','营业成本','毛利率','销售收入同比','营业成本同比','毛利率同比']
                elif sheet_name=='销售模式':
                    new_columns = ['SJLMSMC','销售收入','营业成本','毛利率','销售收入同比','营业成本同比','毛利率同比']
                id_vars = yysr[yysr.columns[0]][0].replace('分产品', 'SJLMYMC').replace('分地区', 'SJLMEMC').replace('分销售模式','SJLMSMC').replace(
                    '销售模式', 'SJLMSMC').replace('分行业', 'SJLMYMC')
                yysr = yysr.rename(columns=dict(zip(yysr.columns, new_columns)))
                yysr = yysr.drop(0)
                yysr = pd.melt(yysr,
                               id_vars=id_vars,
                               value_vars=list(yysr.columns)[1:],
                               var_name='ZBMC',
                               value_name='ZBSJ')
                yysr['JZRQ'] = None
                yysr['ZTYSMC'] = None
                yysr['SJLMY'] = None
                yysr['EPBH'] = None
                yysr['ZBDW'] = None
                yysr['XXFBRQ']=None
                yysr['SJLME'] = None
                yysr['SJLMS'] = None
                yysr['TJKJ'] = None
                yysr['SFYX']=None
                yysr['SFYX'].iloc[:len(yysr)]='FCC000000006'
                conditions = [
                    (yysr['ZBMC'] == '销售收入'),
                    (yysr['ZBMC'] == '营业成本'),
                    (yysr['ZBMC']=='毛利率'),
                    (yysr['ZBMC'] == '销售收入同比'),
                    (yysr['ZBMC'] == '营业成本同比'),
                    (yysr['ZBMC'] == '毛利率同比')
                ]
                values = ['当期值', '当期值', '当期值','当期同比增长率','当期同比增长率','当期同比增减量']
                yysr['TJKJ'] = np.select(conditions, values, default='当期值')
                DW_VALUES = [self.ZBDW,self.ZBDW,'%', '%','%','百分点']
                yysr['ZBDW'] = np.select(conditions, DW_VALUES)
                yysr['TJQJ'] = '年'
                yysr['JYYWLXDM'] = None
                yysr['XXLY'] = None
                yysr['ZBMC'] = yysr['ZBMC'].replace('销售收入同比','销售收入').replace('营业成本同比','营业成本').replace('毛利率同比','毛利率')
                yysr['ZBSJ'] = yysr['ZBSJ'].replace('减少', '-', regex=True).replace('增加', '', regex=True) \
                    .replace('个百分点', '', regex=True).replace('不适用', '', regex=True).replace('/', '', regex=True)\
                    .replace('下降','-',regex=True).replace('增长','',regex=True)
                if id_vars == 'SJLMYMC':
                    yysr['SJLMYDM'] = yysr.merge(self.PRODUCT_CODE, on='SJLMYMC', how='left')['标准代码']
                    yysr['SJLMY'].iloc[:len(yysr)] = '产品'
                    yysr['JYYWLXDM'].iloc[:len(yysr)] = '销售情况（按产品类型）'
                    yysr['SJLMYMC']=yysr['SJLMYMC'].replace('合计', '产品合计',regex=True).replace('总计', '产品总计')\
                    .replace('主营业务收入产品合计','产品合计').replace('收入','')
                elif id_vars == 'SJLMEMC':
                    yysr['SJLME'].iloc[:len(yysr)] = '地区'
                    yysr['JYYWLXDM'].iloc[:len(yysr)] = '销售情况（按区域）'
                    yysr['SJLMEMC']=yysr['SJLMEMC'].replace('合计', '地区合计',regex=True).replace('总计', '地区总计')\
                    .replace('主营业务收入/成本地区合计','地区合计')
                    yysr['SJLMEDM'] = yysr.merge(self.DQ_CODE, on='SJLMEMC', how='left')['DM']
                elif id_vars == 'SJLMSMC':
                    yysr['SJLMS'].iloc[:len(yysr)] = '销售模式'
                    yysr['SJLMSMC']=yysr['SJLMSMC'].replace('合计', '渠道合计').replace('总计', '渠道总计')
                    yysr['JYYWLXDM'].iloc[:len(yysr)] = '销售情况（按销售渠道）'
                yysr['ZBDM'] = yysr.merge(self.ZB_CODE, on='ZBMC', how='left')['ZBDM']
                yysr['EPBH'].iloc[:len(yysr)] = str(self.EPBH)
                yysr['JZRQ'].iloc[:len(yysr)] = self.JZRQ
                yysr['XXLY'].iloc[:len(yysr)] = self.XXLY
                yysr['XXFBRQ'].iloc[:len(yysr)]=self.XXFBRQ
                yysr = yysr.reindex(
                    columns=['EPBH', 'XXLY', 'XXFBRQ', 'JZRQ', 'JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLMYDM',
                             'SJLME', 'SJLMEMC', 'SJLMEDM', 'SJLMS', 'SJLMSMC', 'ZTYSMC', 'ZBDM', 'ZBMC', 'ZBSJ',
                             'ZBDW', 'TJKJ', 'TJQJ'])
                yysr = yysr.replace(' ', '')
                for inde, row in self.BZH.iterrows():
                    yysr=yysr.replace(row['BBZHMC'],row['BZHMC'])
                all_merged_data=pd.concat([all_merged_data,yysr])
            all_merged_data.to_excel(writer, index=False, sheet_name='营收表')
            return all_merged_data
def run_all(ID,Html_file):
    Html_file=Html_file
    find_keyword =[['主营业务分行业情况','营业收入'],['主营业务分产品情况','营业收入'],['营业收入比上年同期增减','分行业'],['营业收入比上年同期增减','分产品'],['营业总收入比上年同期增减','分行业']]
    extractor_instance = PDFTableExtractor(ID=ID, find_keyword=find_keyword,Html_file=Html_file)
    extractor_instance.get_GG()
    extractor_instance.get_EPBH()
    table = extractor_instance.find_keyword_and_extract_table(Html_file=Html_file, find_keyword=find_keyword)
    extractor_instance.get_code()
    extractor_instance.sp(table)
    if table.shape[1] != 0:
        table=extractor_instance.xggs()
        return table
    else:
        print('未找到关键词')
    return pd.DataFrame()
if __name__ == '__main__':
    ID = '767744242959'
    Html_file = r'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\767744242959.HTML'
    run_all(ID,Html_file)